package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.SolarEnergyDto;
import com.hualude.roadsign.dto.SolarEnergyFaultDto;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by shao-yikun on 2017/7/11.
 */
public class SolarEnergyRepositoryImpl {
    @PersistenceContext
    private EntityManager em;

    public List<SolarEnergyDto> getSolarEnergy4DT(DTRequestDto dtRequestDto,
                                                  String actorIds,
                                                  Integer orgId,
                                                  Boolean audit,
                                                  String startRoad,
                                                  String endRoad,
                                                  String roadName,
                                                  Integer alarmBinding) throws Exception {
        String sql = "select se.*, ur.UserName as solarEnergyCreatorName, " +
                "og1.OrgName as solarEnergyOrgName, og2.OrgName as solarEnergyMainOrgName,a.DeviceState " +
                "from (tb_solar_energy se, tb_user ur, tb_org og1, tb_org og2) " +
                "left join tb_alarm a on (se.AlarmDeviceId=a.DeviceId) " +
                "where ur.UserId = se.SolarEnergyCreatorId " +
                "and og1.OrgId = se.SolarEnergyOrgId and og2.OrgId = se.SolarEnergyMainOrgId and " +
                "se.SolarEnergyRoadName like '%" + roadName + "%' and se.SolarEnergyStartRoad like '%" + startRoad + "%' " +
                "and se.SolarEnergyEndRoad like '%" + endRoad + "%' and se.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
        if (audit)
            sql += "and solarEnergyIsComplete = 0 ";
        if (alarmBinding != null) {
            if (alarmBinding == 1)
                sql += " and AlarmDeviceId is not NULL ";
            else if (alarmBinding == 2)
                sql += " and AlarmDeviceId is NULL ";
        }
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and se.SolarEnergyMainOrgId=" + orgId + " ";
        else
            sql += "and se.SolarEnergyOrgId = " + orgId + " ";
        if (startRoad != "" || endRoad != "") {
            sql += "union select se.*, ur.UserName as solarEnergyCreatorName, " +
                    "og1.OrgName as solarEnergyOrgName, og2.OrgName as solarEnergyMainOrgName,a.DeviceState " +
                    "from (tb_solar_energy se, tb_user ur, tb_org og1, tb_org og2) " +
                    "left join tb_alarm a on (se.AlarmDeviceId=a.DeviceId) " +
                    "where ur.UserId = se.SolarEnergyCreatorId " +
                    "and og1.OrgId = se.SolarEnergyOrgId and og2.OrgId = se.SolarEnergyMainOrgId and " +
                    "se.SolarEnergyRoadName like '%" + roadName + "%' and se.SolarEnergyStartRoad like '%" + endRoad + "%' " +
                    "and se.SolarEnergyEndRoad like '%" + startRoad + "%' and se.ProjectNumber like '%" + dtRequestDto.getSearch() + "%'";
            if (actorIds.indexOf('1') >= 0)
                sql += "";
            else if (actorIds.indexOf('7') >= 0)
                sql += "and se.SolarEnergyMainOrgId=" + orgId + " ";
            else
                sql += "and se.SolarEnergyOrgId = " + orgId + " ";
            if (alarmBinding != null) {
                if (alarmBinding == 1)
                    sql += " and AlarmDeviceId is not NULL ";
                else if (alarmBinding == 2)
                    sql += " and AlarmDeviceId is NULL ";
            }
        }
        sql += "order by SolarEnergyInstallTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SolarEnergyDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getSolarEnergy4DTCount(DTRequestDto dtRequestDto,
                                          String actorIds,
                                          Integer orgId,
                                          Boolean audit,
                                          String startRoad,
                                          String endRoad,
                                          String roadName,
                                          Integer alarmBinding) throws Exception {
        String sql1 = "select count(*) " +
                "from tb_solar_energy se, tb_user ur, tb_org og1, tb_org og2 " +
                "where ur.UserId = se.SolarEnergyCreatorId " +
                "and og1.OrgId = se.SolarEnergyOrgId and og2.OrgId = se.SolarEnergyMainOrgId and " +
                "se.SolarEnergyRoadName like '%" + roadName + "%' and se.SolarEnergyStartRoad like '%" + startRoad + "%' " +
                "and se.SolarEnergyEndRoad like '%" + endRoad + "%' and se.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
        if (audit)
            sql1 += "and solarEnergyIsComplete = 0 ";
        if (alarmBinding != null) {
            if (alarmBinding == 1)
                sql1 += " and AlarmDeviceId is not NULL ";
            else if (alarmBinding == 2)
                sql1 += " and AlarmDeviceId is NULL ";
        }
        if (actorIds.indexOf('1') >= 0)
            sql1 += "";
        else if (actorIds.indexOf('7') >= 0)
            sql1 += "and se.SolarEnergyMainOrgId=" + orgId + " ";
        else
            sql1 += "and se.SolarEnergyOrgId = " + orgId + " ";

        Query query1 = em.createNativeQuery(sql1);
        Integer count1 = Integer.parseInt(query1.getSingleResult().toString());
        Integer count2 = 0;
        if (startRoad != "" || endRoad != "") {
            String sql2 = "select count(*) " +
                    "from tb_solar_energy se, tb_user ur, tb_org og1, tb_org og2 " +
                    "where ur.UserId = se.SolarEnergyCreatorId " +
                    "and og1.OrgId = se.SolarEnergyOrgId and og2.OrgId = se.SolarEnergyMainOrgId and " +
                    "se.SolarEnergyRoadName like '%" + roadName + "%' and se.SolarEnergyStartRoad like '%" + endRoad + "%' " +
                    "and se.SolarEnergyEndRoad like '%" + startRoad + "%' and se.ProjectNumber like '%" + dtRequestDto.getSearch() + "%'";
            if (alarmBinding != null) {
                if (alarmBinding == 1)
                    sql2 += " and AlarmDeviceId is not NULL ";
                else if (alarmBinding == 2)
                    sql2 += " and AlarmDeviceId is NULL ";
            }
            if (actorIds.indexOf('1') >= 0)
                sql2 += "";
            else if (actorIds.indexOf('7') >= 0)
                sql2 += "and se.SolarEnergyMainOrgId=" + orgId + " ";
            else
                sql2 += "and se.SolarEnergyOrgId = " + orgId + " ";
            Query query2 = em.createNativeQuery(sql2);
            count2 = Integer.parseInt(query2.getSingleResult().toString());
        }


        return count2 + count1;

    }

    public List<SolarEnergyDto> getSolarEnergy4APP(String actorIds,
                                                   Integer orgId,
                                                   Integer mCount,
                                                   Integer mSize,
                                                   String city,
                                                   String startRoad,
                                                   String endRoad,
                                                   String roadName,
                                                   String alarmType) throws Exception {
        String sql = "select se.*, ur.UserName as solarEnergyCreatorName, " +
                "og1.OrgName as solarEnergyOrgName, og2.OrgName as solarEnergyMainOrgName,a.DeviceState " +
                "from (tb_solar_energy se, tb_user ur, tb_org og1, tb_org og2) " +
                "left join tb_alarm a on (se.AlarmDeviceId=a.DeviceId) " +
                "where ur.UserId = se.SolarEnergyCreatorId " +
                "and og1.OrgId = se.SolarEnergyOrgId and og2.OrgId = se.SolarEnergyMainOrgId " +
                "and se.SolarEnergyStartRoad like '%" + startRoad + "%' and se.SolarEnergyEndRoad like '%" + endRoad + "%' " +
                "and se.SolarEnergyRoadName like '%" + roadName + "%' and se.SolarEnergyCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and se.SolarEnergyMainOrgId=" + orgId + " ";
        else
            sql += "and se.SolarEnergyOrgId = " + orgId + " ";
        if (alarmType.contains("0"))
            sql += " and se.AlarmDeviceId is NULL ";
        else if (alarmType.contains("1"))
            sql += " and se.AlarmDeviceId is not NULL ";
        else
            sql += "";
        if (startRoad != "" || endRoad != "") {
            sql += "union select se.*, ur.UserName as solarEnergyCreatorName," +
                    "og1.OrgName as solarEnergyOrgName, og2.OrgName as solarEnergyMainOrgName,a.DeviceState " +
                    "from (tb_solar_energy se, tb_user ur, tb_org og1, tb_org og2) " +
                    "left join tb_alarm a on (se.AlarmDeviceId=a.DeviceId) " +
                    "where ur.UserId = se.SolarEnergyCreatorId " +
                    "and og1.OrgId = se.SolarEnergyOrgId and og2.OrgId = se.SolarEnergyMainOrgId " +
                    "and se.SolarEnergyStartRoad like '%" + endRoad + "%' and se.SolarEnergyEndRoad like '%" + startRoad + "%' " +
                    "and se.SolarEnergyRoadName like '%" + roadName + "%' and se.SolarEnergyCity like '%" + city + "%' ";
            if (actorIds.indexOf('1') >= 0)
                sql += "";
            else if (actorIds.indexOf('7') >= 0)
                sql += "and se.SolarEnergyMainOrgId=" + orgId + " ";
            else
                sql += "and se.SolarEnergyOrgId = " + orgId + " ";
            if (alarmType.contains("0"))
                sql += " and se.AlarmDeviceId is NULL ";
            else if (alarmType.contains("1"))
                sql += " and se.AlarmDeviceId is not NULL ";
            else
                sql += "";
        }
        sql += "order by concat(SolarEnergyInstallTime, solarenergyId) desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SolarEnergyDto.class));
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<String> getRoadByRoadName(String roadName, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT se.SolarEnergyStartRoad AS solarEnergyStartRoad\n" +
                "FROM tb_solar_energy se\n" +
                "WHERE se.SolarEnergyRoadName LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and se.SolarEnergyMainOrgId=" + orgId + " ";
            else
                sql += " and se.SolarEnergyOrgId=" + orgId + " ";
        }
        sql += " UNION SELECT se.SolarEnergyEndRoad AS solarEnergyStartRoad\n" +
                "FROM tb_solar_energy se\n" +
                "WHERE se.SolarEnergyRoadName LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and se.SolarEnergyMainOrgId=" + orgId + " ";
            else
                sql += " and se.SolarEnergyOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<String> getSectionByRoadName(String roadName, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT se.SolarEnergyStartRoad AS solarEnergyStartRoad\n" +
                "FROM tb_solar_energy se\n" +
                "WHERE se.SolarEnergyEndRoad LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and se.SolarEnergyMainOrgId=" + orgId + " ";
            else
                sql += " and se.SolarEnergyOrgId=" + orgId + " ";
        }
        sql += " UNION SELECT se.SolarEnergyEndRoad AS solarEnergyStartRoad\n" +
                "FROM tb_solar_energy se\n" +
                "WHERE se.SolarEnergyStartRoad LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and se.SolarEnergyMainOrgId=" + orgId + " ";
            else
                sql += " and se.SolarEnergyOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<SolarEnergyFaultDto> getSolarCoordinate4Map(Integer orgId, Integer actorId) throws Exception {
        String sql = "SELECT soe.SolarEnergyId,soe.SolarEnergyLongitude,soe.SolarEnergyLatitude,soe.SolarEnergyPhoto\n" +
                "FROM tb_solar_energy soe WHERE soe.SolarEnergyIsComplete=1 ";
        if (actorId != 1) {
            sql += " and soe.SolarEnergyOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SolarEnergyFaultDto.class));
        return query.getResultList();
    }


    public List<SolarEnergyDto> getSolarEnergyById(Integer solarEnergyId) throws Exception {
        String sql = "select se.* ,o1.orgName as solarEnergyOrgName,o2.OrgName as solarEnergyMainOrgName," +
                " u.userName as solarEnergyCreatorName from tb_solar_energy se,tb_org o1,tb_user u,tb_org o2 " +
                "where o1.orgId=se.SolarEnergyOrgId and o2.orgId=se.SolarEnergyMainOrgId and u.UserId=se.SolarEnergyCreatorId" +
                " and se.SolarEnergyId=" + solarEnergyId + " ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SolarEnergyDto.class));
        return query.getResultList();
    }

    public List<SolarEnergyDto> getSolarEnergyCoordinate4Map(Integer orgId, Integer orgType, Integer actorId) throws Exception {
        String sql = "select solarEnergyId, solarEnergyLongitude as longitude,solarEnergyLatitude as latitude from tb_solar_energy ";
        if (actorId != 1) {
            if (orgType == 0)
                sql += "where solarEnergyOrgId=" + orgId + "";
            if (orgType == 1)
                sql += "where solarEnergyMainOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SolarEnergyDto.class));
        return query.getResultList();
    }

    public List<String> getCity4SolarEnergy4App(Integer actorId, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "select DISTINCT SolarEnergyCity from tb_solar_energy ";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " where SolarEnergyMainOrgId=" + orgId + " ";
            else
                sql += " where SolarEnergyOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<String> getRoad4SolarEnergy(Integer actorId, Integer orgId, String city, Integer mCount, Integer mSize) throws Exception {
        String sql = "select DISTINCT SolarEnergyRoadName from  tb_solar_energy where SolarEnergyCity like '%" + city + "%' ";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and SolarEnergyMainOrgId=" + orgId + " ";
            else
                sql += " and SolarEnergyOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<String> getSectionByRoadName4App(String roadName, String solarEnergyRoadName, String city, Integer actorId,
                                                 Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "SELECT se.SolarEnergyStartRoad AS solarEnergyStartRoad\n" +
                "FROM tb_solar_energy se\n" +
                "WHERE se.SolarEnergyEndRoad LIKE '%" + roadName + "%' and se.SolarEnergyCity LIKE '%" + city + "%' " +
                "and se.SolarEnergyRoadName like '%" + solarEnergyRoadName + "%' ";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and se.SolarEnergyMainOrgId=" + orgId + " ";
            else
                sql += " and se.SolarEnergyOrgId=" + orgId + " ";
        }
        sql += " UNION SELECT se.SolarEnergyEndRoad AS solarEnergyStartRoad\n" +
                "FROM tb_solar_energy se\n" +
                "WHERE se.SolarEnergyStartRoad LIKE '%" + roadName + "%' and se.SolarEnergyCity LIKE '%" + city + "%' " +
                "and se.SolarEnergyRoadName like '%" + solarEnergyRoadName + "%' ";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and se.SolarEnergyMainOrgId=" + orgId + " ";
            else
                sql += " and se.SolarEnergyOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<String> getStartRoad4App(String roadName, String city, Integer actorId, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "SELECT se.SolarEnergyStartRoad AS solarEnergyStartRoad\n" +
                "FROM tb_solar_energy se\n" +
                "WHERE se.SolarEnergyRoadName LIKE '%" + roadName + "%' and se.SolarEnergyCity like '%" + city + "%' ";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and se.SolarEnergyMainOrgId=" + orgId + " ";
            else
                sql += " and se.SolarEnergyOrgId=" + orgId + " ";
        }
        sql += " UNION SELECT se.SolarEnergyEndRoad AS solarEnergyStartRoad\n" +
                "FROM tb_solar_energy se\n" +
                "WHERE se.SolarEnergyRoadName LIKE '%" + roadName + "%' and se.SolarEnergyCity like '%" + city + "%' ";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and se.SolarEnergyMainOrgId=" + orgId + " ";
            else
                sql += " and se.SolarEnergyOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();

    }

    public List<SolarEnergyDto> getSolarEnergy4Map4App(Integer orgId,
                                                       String actorIds,
                                                       double neLongitude,
                                                       double neLatitude,
                                                       double swLongitude,
                                                       double swLatitude) throws Exception {
        String sql = "select * from tb_solar_energy ";
        sql += "where solarEnergyIsComplete = 1 " +
                "and solarEnergyLongitude < :neLongitude and solarEnergyLongitude > :swLongitude " +
                "and solarEnergyLatitude < :neLatitude and solarEnergyLatitude > :swLatitude ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and solarEnergyMainOrgId=" + orgId + " ";
        else
            sql += "and solarEnergyOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        query.setParameter("neLongitude", String.valueOf(neLongitude));
        query.setParameter("swLongitude", String.valueOf(swLongitude));
        query.setParameter("neLatitude", String.valueOf(neLatitude));
        query.setParameter("swLatitude", String.valueOf(swLatitude));
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SolarEnergyDto.class));
        return query.getResultList();
    }

    public List<SolarEnergyFaultDto> getSolarEnergyByXY(String longitude, String latitude) throws Exception {
        String sql = "select soe.solarEnergyId,soe.solarEnergyPhoto," +
                "soe.solarEnergyLongitude,soe.solarEnergyLatitude,soe.solarEnergyProvince,soe.solarEnergyCity," +
                "soe.solarEnergyArea,soe.solarEnergyRoadName,soe.solarEnergyStartRoad,soe.solarEnergyEndRoad,soe.solarEnergyType," +
                "soe.solarEnergyStyle,soe.solarEnergyMaterial,soe.solarEnergyInstallOrg,soe.solarEnergyCreatorId," +
                "soe.solarEnergyOrgId,soe.solarEnergyMainOrgId " +
                "from tb_solar_energy soe where soe.solarEnergyLongitude=" + longitude + " and soe.solarEnergyLatitude=" + latitude + "";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SolarEnergyFaultDto.class));
        return query.getResultList();
    }

    public List<SolarEnergyDto> getDeviceInfo(Integer deviceId) throws Exception {
        String sql = "select se.solarEnergyId,se.SolarEnergyProvince,se.SolarEnergyCity,se.SolarEnergyArea,se.SolarEnergyRoadName,se.SolarEnergyStartRoad,se.SolarEnergyEndRoad," +
                "se.SolarEnergyPhoto,se.SolarEnergyOrgId,se.SolarEnergyMainOrgId,se.AlarmDeviceId,o1.OrgName as solarEnergyOrgName,o2.OrgName as solarEnergyMainOrgName," +
                "se.ProjectNumber,se.SolarEnergyRegionId,se.SolarEnergyLongitude,se.SolarEnergyLatitude,se.SolarEnergyType,se.SolarEnergyStyle,se.SolarEnergyMaterial," +
                "se.SolarEnergyBrand,se.SolarEnergyInstallTime,se.SolarEnergyInstallOrg,se.SolarEnergyRemark,se.SolarEnergyCreatorId,se.SolarEnergyModifierId,se.SolarEnergyIsComplete," +
                "se.SolarEnergyADeviceId,se.AlarmCount " +
                "from tb_solar_energy se,tb_org o1,tb_org o2 where se.SolarEnergyOrgId = o1.OrgId and se.SolarEnergyMainOrgId = o2.OrgId and se.SolarEnergyId = " + deviceId + " ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SolarEnergyDto.class));
        return query.getResultList();
    }


}
